﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Spring.Data.Generic;
using Spring.Data.Common;
using Spring.Data.Objects.Generic;
using Spring.Transaction;
using Kpmg.CompanyInfo;

namespace MySearch
{
    public static class ProfileDAO
    {
        private static AdoTemplate template = AppConfigurer.AdoTemplate;

        internal static Profile GetProfile(string profileCode)
        {
            string sqlGet = " select Profile_Code, Profile_Type, Description, Status, Source_Type "
                    + " from Profile "
                    + " where Profile_Code=@code ";
            return FieldAndMemberDAOBase.Get<Profile>(profileCode, sqlGet, MappingRow);
        }

        internal static bool CheckProfileCode(string profileCode)
        {
            string sqlSel = " select Profile_Code, Profile_Type, Description, Status, Source_Type "
                    + " from Profile "
                    + " where Profile_Code=@profileCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("profileCode", DbType.String).Value = profileCode;

            string strCode = null;
            template.QueryWithRowCallbackDelegate(CommandType.Text, sqlSel, delegate(IDataReader dr)
            {
                strCode = dr.GetString(0);

            }, p);

            return !string.IsNullOrEmpty(strCode);
        }

        internal static IList<string> ListProfileCode(string staffNo)
        {
            string strSql = " select [Profile_Code] from Profile_Member where Staff_No=@staffNo ";

            IDbParameters p1 = template.CreateDbParameters();
            p1.Add("staffNo", DbType.String).Value = staffNo;

            IList<string> lstRet = new List<string>();

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader dr)
            {
                lstRet.Add(dr.GetString(0));

            }, p1);

            return lstRet;
        }

        internal static IList<string> ListServiceTeamProfileCode()
        {
            string strSql = " select [Profile_Code] from Profile where profile_type='ServiceTeam' ";

            IList<string> lstRet = new List<string>();

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader dr)
            {
                lstRet.Add(dr.GetString(0));

            });

            return lstRet;

        }

        internal static void CreateProfile(Profile profile)
        {
            string strIns = " insert into Profile values(@code, @type, @desc, @status, @source) ";
            template.ExecuteNonQuery(CommandType.Text, strIns, CreateParameters(profile));

            FieldAndMemberDAOBase.InsertFieldAndMember(profile.ProfileCode, profile);
        }

        internal static void UpdateProfile(Profile profile, bool isFullyUpdated)
        {
            #region update table: Profile
            string strUpd = "update Profile set Profile_Type=@type, Description=@desc, Status=@status, "
                + " Source_Type=@source "
                + " where Profile_Code=@code ";
            template.ExecuteNonQuery(CommandType.Text, strUpd, CreateParameters(profile));
            #endregion

            if (isFullyUpdated)
            {
                // delete field and member
                string sqlDelete = "delete from Profile_Field where Profile_Code=@Code;"
                    + "delete from Profile_Member where Profile_Code=@Code";
                template.ExecuteNonQuery(CommandType.Text, sqlDelete, CreateParameters(profile, false));

                // insert field and member
                FieldAndMemberDAOBase.InsertFieldAndMember(profile.ProfileCode, profile);
            }
        }

        internal static void UpdateField(Profile profile)
        {
            // delete field and member
            string sqlDelete = "delete from Profile_Field where Profile_Code=@Code;";
            template.ExecuteNonQuery(CommandType.Text, sqlDelete, CreateParameters(profile, false));

            // insert field and member
            FieldAndMemberDAOBase.InsertField(profile.ProfileCode, profile);
        }

        internal static void UpdateMember(Profile profile)
        {
            // delete field and member
            string sqlDelete = "delete from Profile_Member where profile_Code=@Code";
            template.ExecuteNonQuery(CommandType.Text, sqlDelete, CreateParameters(profile, false));

            // insert field and member
            FieldAndMemberDAOBase.InsertMember(profile.ProfileCode, profile);
        }

        #region private

        private static Profile MappingRow(IDataReader dr, int rowNumber)
        {
            Profile profile = new Profile();
            profile.ProfileCode = dr.GetString(0);
            profile.ProfileType = (ProfileType)Enum.Parse(typeof(ProfileType), dr.GetString(1));
            profile.Description = dr.GetString(2);
            profile.Status = (ProfileStatus)Enum.Parse(typeof(ProfileStatus), dr.GetString(3), true);
            profile.Source = (ProfileSource)Enum.Parse(typeof(ProfileSource), dr.GetString(4));

            return profile;
        }

        private static IDbParameters CreateParameters(Profile profile, bool isAll = true)
        {
            IDbParameters paras = template.CreateDbParameters();
            paras.Add("Code", DbType.String).Value = profile.ProfileCode;

            if (!isAll)
                return paras;

            paras.Add("type", DbType.String).Value = profile.ProfileType.ToString();
            paras.Add("desc", DbType.String).Value = profile.Description;
            paras.Add("status", DbType.String).Value = profile.Status;
            paras.Add("source", DbType.String).Value = profile.Source;
            return paras;
        }

        #endregion
    }
}
